﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;
using System.Net.Mail;
using Excel = Microsoft.Office.Interop.Excel;
using System.Threading;
using System.Collections;
using System.IO.Compression;
using Ionic.Zip;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }
        private CurrencyManager cm;
        private const string namechcol = "Флаг";
        private SqlConnection conf;
        private SqlConnection conn;
        private DataTable dt_ph_code;               //- коды аптек для прайсов
        private DataTable dt_ph_name;               //- для хранения названий аптек
        private DataTable dt_sup_code;              //- коды поставщиков для прайсов
        private DataTable dt_rep = new DataTable();
        private DataTable dt_price;                 //- таблица с прайсами на крайнюю дату
        private delegate void Deleg();
        private Deleg del;
        private DataTable dt_pharm = new DataTable();
        private DataTable dt_sup = new DataTable();
        private struct Pharmflag { public int code; public bool fl; public int baseid; public int cityid; }
        private Pharmflag[] phfl = new Pharmflag[] { };
        private Pharmflag[] supfl = new Pharmflag[] { };
        private string folder = "";
        private DataTable dt_email = new DataTable();
        private SqlConnection pharmolapdw;
        private DataTable dt_rep_total = new DataTable();
        private DataTable dt_fail_tot = new DataTable();
        private DataTable dt_fail = new DataTable();
        private bool flag = true;                   //- флаг привязанные/непривязанные позиции

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=AllPriceDW;Integrated Security=True");
                pharmolapdw = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
                conf = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=SecurityFA;Integrated Security=True");
                
                if(conn.State == ConnectionState.Closed) conn.Open();
                dt_pharm = DownloadData(conn, dt_pharm, "pas_PharmSel");
                dt_sup = DownloadData(conn, dt_sup, "pas_SupplSel_DAC");
                foreach (DataRow dr in dt_pharm.Rows)
                {
                    Array.Resize(ref phfl, phfl.Length + 1);
                    phfl[phfl.Length - 1].code = Convert.ToInt32(dr["Код аптеки"]);
                    phfl[phfl.Length - 1].fl = Convert.ToBoolean(dr[namechcol]);
                    phfl[phfl.Length - 1].baseid = Convert.ToInt32(dr["ИД базы"]);
                    phfl[phfl.Length - 1].cityid = Convert.ToInt32(dr["ИД города"]);
                }
                foreach (DataRow dr in dt_sup.Rows)
                {
                    Array.Resize(ref supfl, supfl.Length + 1);
                    supfl[supfl.Length - 1].code = Convert.ToInt32(dr["Код поставщика"]);
                    supfl[supfl.Length - 1].fl = Convert.ToBoolean(dr[namechcol]);
                    supfl[supfl.Length - 1].baseid = Convert.ToInt32(dr["ИД базы"]);
                    supfl[supfl.Length - 1].cityid = Convert.ToInt32(dr["ИД города"]);
                }
                textBox1.Text = System.Environment.UserName + "@apteka-rayona.ru";

                if (conf.State == ConnectionState.Closed) conf.Open();
                SqlCommand comf = conf.CreateCommand();
                comf.CommandType = CommandType.StoredProcedure;
                comf.CommandText = "FolderDefaultSel";
                comf.Parameters.AddWithValue("@tool",ToolName);
                DataTable dt_folder = new DataTable();
                dt_folder.Load(comf.ExecuteReader());
                if(dt_folder.Rows.Count > 0) folder = dt_folder.Rows[0]["Folder"].ToString();
                SqlCommand em = conf.CreateCommand();
                em.CommandType = CommandType.StoredProcedure;
                em.CommandText = "pas_EmailUser";
                DataTable dt_mail = new DataTable();
                dt_mail.Load(em.ExecuteReader());
                textBox1.Text = dt_mail.Rows[0][0].ToString();
                pharmolapdw = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
                dt_sup_code = new DataTable();
                dt_sup_code.Columns.Add("Код поставщика");
                dt_sup_code.Columns.Add("ИД базы");
                dt_sup_code.Columns.Add();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private Pharmflag[] PharmFlUpd(Pharmflag[] pfl, DataTable dt, string codename)
        {
            foreach (DataRow dr in dt.Rows)
            {
                for(int i = 0; i < pfl.Length; i++)
                    if (pfl[i].code == Convert.ToInt32(dr[codename]) && pfl[i].baseid == Convert.ToInt32(dr["ИД базы"]) && pfl[i].cityid == Convert.ToInt32(dr["ИД города"]))
                        pfl[i].fl = Convert.ToBoolean(dr[namechcol]);

            }
            return pfl;
        }
        private DataTable DownloadData(SqlConnection con, DataTable dt, string proc)
        {
            SqlCommand pharm = con.CreateCommand();
            pharm.CommandText = proc;
            pharm.CommandType = CommandType.StoredProcedure;
            dt = new DataTable();
            dt.Load(pharm.ExecuteReader());
            dt.Columns[0].ReadOnly = false;
            return dt;
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dt_rep.Clear();
            dt_rep.Columns.Clear();
            dataGridView2.DataSource = null;
            dt_fail.Clear();
            dt_fail.Columns.Clear();
            checkBox4.Checked = false;
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null);
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
            dataGridView2.Enabled = false;
        }

        private void StartCreateRep1(object sender, EventArgs e)
        {
            //- формирование прайса
            dataGridView1.DataSource = null;
            dataGridView2.DataSource = null;
            del = new Deleg(CreateRep1);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc1), null);
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }


        private void CallBackFunc1(IAsyncResult aRes)
        {
            //- формирование прайса 
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            if (dt_price != null)
            {
                dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_price.DefaultView));
                statusStrip1.Invoke((Func<string>)(() => toolStripStatusLabel1.Text = "Всего строк: " + dataGridView1.RowCount.ToString()));
                statusStrip1.Invoke((Func<string>)(() => toolStripStatusLabel2.Text = "Всего аптек: " + dt_ph_code.Rows.Count.ToString()));
            }
        }


        private void CallBackFunc(IAsyncResult aRes)
        {
            try
            {
                del.EndInvoke(aRes);
                panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
                dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
                dataGridView2.Invoke((Func<bool>)(() => dataGridView2.Enabled = true));
                this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
                if (dt_rep.Rows.Count > 0)
                {
                    dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
                }
                dataGridView2.Invoke((Func<object>)(() => dataGridView2.DataSource = dt_fail.DefaultView));
                statusStrip1.Invoke((Func<string>)(() => toolStripStatusLabel1.Text = "Всего строк: " + dataGridView1.RowCount.ToString()));
                statusStrip1.Invoke((Func<string>)(() => toolStripStatusLabel2.Text = "Всего аптек: " + dataGridView2.RowCount.ToString()));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void CreateRep1()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                dt_price = new DataTable();
                //- формирование прайса
                dt_ph_code = new DataTable();
                dt_ph_name = new DataTable();
                dt_ph_name.Columns.Add("Город");
                dt_ph_name.Columns.Add("Код аптеки");
                dt_ph_name.Columns.Add("Аптека");
                dt_ph_code.Columns.Add();
                dt_ph_code.Columns.Add();
                dt_ph_code.Columns.Add();
                foreach (DataRow dr in dt_pharm.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]))
                    {
                        dt_ph_code.Rows.Add(dr[2], dr[7], dr[8]);
                        dt_ph_name.Rows.Add(dr[1], dr[2], dr[3]);
                    }
                }
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[3];
                par[0].name = "@flag";
                par[0].value = flag;
                par[1].name = "@tabSuppl";
                par[1].value = dt_sup_code;
                par[2].name = "@tabPharm";
                par[2].value = dt_ph_code;
                dt_price = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "PriceGenerate", 300, par);
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            conn.Close();
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                Guid guid = Guid.NewGuid();
                dt_rep = null;
                dt_rep = new DataTable();
                dt_rep.Load(RepProc(conn, guid));
                dt_rep.Columns[0].ReadOnly = false;

                dt_fail_tot = null;
                dt_fail_tot = new DataTable();
                dt_fail_tot.Columns.Add("Код аптеки");
                dt_fail_tot.Columns.Add("Всего отказов");
                DataRow[] foundRows = dt_rep.Select("Товар = 'Все'");

                foreach (DataRow dr in foundRows)
                {
                    object[] row = new object[2] { dr[1], dr[5] };
                    dt_fail_tot.Rows.Add(row);
                }
                
                for (int i = 0; i < dt_rep.Rows.Count; i++)
                {
                    if (Convert.ToInt32(dt_rep.Rows[i]["Код товара"]) == 0)
                        dt_rep.Rows[i].Delete();
                }

                dt_rep_total = null;
                dt_rep_total = new DataTable();
                dt_rep_total.Load(RepProcTotal(conn,guid));

                var results = from table1 in dt_rep_total.AsEnumerable()
                              join table2 in dt_fail_tot.AsEnumerable() on Convert.ToInt32(table1["Код аптеки"]) equals Convert.ToInt32(table2["Код аптеки"])
                              select new
                              {
                                  col1 = table1["Код аптеки"],
                                  col2 = table1["Аптека"],
                                  col3 = table1["Всего позиций"],
                                  col4 = table2["Всего отказов"],
                                  col5 = table1["Всего заявок"],
                                  col6 = table1["Всего товаров"]
                              };
                dt_fail = null;
                dt_fail = new DataTable();
                dt_fail.Columns.Add("Код аптеки");
                dt_fail.Columns.Add("Аптека");
                dt_fail.Columns.Add("Всего позиций");
                dt_fail.Columns.Add("Всего отказов");
                dt_fail.Columns.Add("Всего заявок");
                dt_fail.Columns.Add("Всего товаров");
                foreach(var it in results)
                {
                    object[] row = new object[6] { it.col1, it.col2, it.col3, it.col4, it.col5, it.col6 };
                    dt_fail.Rows.Add(row);
                }
                dt_fail_tot = null;
                dt_rep_total = null;
                SqlCommand sqlcom = conn.CreateCommand();
                sqlcom.CommandType = CommandType.StoredProcedure;
                sqlcom.CommandText = @"pas_GUID_SPCB_Ins";
                sqlcom.CommandTimeout = 30;
                sqlcom.Parameters.AddWithValue("@guid", guid);
                sqlcom.Parameters.AddWithValue("@del", 1);
                if (conn.State == ConnectionState.Closed) conn.Open();
                sqlcom.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            conn.Close();
        }

        private SqlDataReader RepProc(SqlConnection sqlcon, Guid guid)
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"pas_GUID_SPCB_Ins"; 
            sqlcom.CommandTimeout = 30;
            sqlcom.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
            sqlcom.Parameters.Add("@cityid", SqlDbType.Int);
            sqlcom.Parameters.Add("@pharmcode", SqlDbType.Int);
            sqlcom.Parameters.Add("@supcode", SqlDbType.Int);
            sqlcom.Parameters.Add("@baseid", SqlDbType.Int);
            sqlcom.Parameters.AddWithValue("@del", 0);

            foreach (DataRow sup in dt_sup.Rows)
                if(Convert.ToBoolean(sup[namechcol]))
                    foreach(DataRow ph in dt_pharm.Rows)
                        if (Convert.ToBoolean(ph[namechcol]) && Convert.ToInt32(ph["ИД базы"]) == Convert.ToInt32(sup["ИД базы"]) && Convert.ToInt32(ph["ИД города"]) == Convert.ToInt32(sup["ИД города"]))
                        {
                            sqlcom.Parameters["@guid"].Value = guid;
                            sqlcom.Parameters["@cityid"].Value = ph["ИД города"];
                            sqlcom.Parameters["@pharmcode"].Value = ph["Код аптеки"];
                            sqlcom.Parameters["@supcode"].Value = sup["Код поставщика"];
                            sqlcom.Parameters["@baseid"].Value = ph["ИД базы"]; ;
                            if (sqlcon.State == ConnectionState.Closed) sqlcon.Open();
                            sqlcom.ExecuteNonQuery();
                        }

            SqlCommand rep = sqlcon.CreateCommand();
            rep.CommandType = CommandType.StoredProcedure;
            rep.CommandText = "pas_PriceFailReport";
            rep.CommandTimeout = 600;
            rep.Parameters.AddWithValue("@guid", guid);
            rep.Parameters.AddWithValue("@onlyfail", checkBox1.Checked);

            return rep.ExecuteReader();
        }
        private SqlDataReader RepProcTotal(SqlConnection sqlcon, Guid guid)
        {
            SqlCommand rep = sqlcon.CreateCommand();
            rep.CommandType = CommandType.StoredProcedure;
            rep.CommandText = "pas_PriceFailReportTotal";
            rep.CommandTimeout = 300;
            rep.Parameters.AddWithValue("@guid", guid);
            if (sqlcon.State == ConnectionState.Closed) sqlcon.Open();

            return rep.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            PharmFilter.PharmFilter p = new PharmFilter.PharmFilter();
            p.dataGridView1.DataSource = dt_pharm.DefaultView;
            p.namechcol = namechcol;
            p.Text = "Фильтр по аптекам";
            p.sortcoumn = "Город";
            p.StartPosition = FormStartPosition.CenterScreen;
            DialogResult res = p.ShowDialog();
            p.Close();
            phfl = PharmFlUpd(phfl, dt_pharm, "Код аптеки");

            if(res == DialogResult.OK) DownloadReq();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            PharmFilter.PharmFilter p = new PharmFilter.PharmFilter();
            p.dataGridView1.DataSource = dt_sup.DefaultView;
            p.namechcol = namechcol;
            p.Text = "Фильтр по поставщикам";
            p.sortcoumn = "Город";
            p.StartPosition = FormStartPosition.CenterScreen;
            DialogResult res = p.ShowDialog();
            p.Close();
            supfl = PharmFlUpd(supfl,dt_sup,"Код поставщика");

            if(res == DialogResult.OK) DownloadPrice();
        }

        private void DownloadReq()
        {
            int i = 0;
            string[] bstr = new string[3] { "", "", "" };

            foreach(DataRow dr in dt_pharm.Rows)
                if (Convert.ToBoolean(dr[namechcol]))
                {
                    switch (Convert.ToInt32(dr["ИД базы"]))
                    {
                        case 1:
                            bstr[0] += dr["Код аптеки"].ToString() + ",";
                            break;
                        case 2:
                            bstr[1] += dr["Код аптеки"].ToString() + ",";
                            break;
                        case 3:
                            bstr[2] += dr["Код аптеки"].ToString() + ",";
                            break;
                    }
                    i++;
                }
      
            if (i > 0)
            {
                SqlCommand req = conn.CreateCommand();
                req.CommandText = "pas_RequestDownload";
                req.CommandType = CommandType.StoredProcedure;
                req.CommandTimeout = 300;
                req.Parameters.Add("@baseid", SqlDbType.Int);
                req.Parameters.Add("@phcode", SqlDbType.VarChar, 1000);

                for(int j = 0; j < bstr.Length; j++)
                {
                    if (bstr[j] != "")
                    {
                        req.Parameters["@baseid"].Value = j + 1;
                        req.Parameters["@phcode"].Value = bstr[j].Substring(0, bstr[j].Length - 1);
                        if (conn.State == ConnectionState.Closed) conn.Open();
                        req.ExecuteNonQuery();
                    }
                }              
                dt_pharm = DownloadData(conn, dt_pharm, "pas_PharmSel");
                dt_pharm = DataTableUpd(dt_pharm, phfl, "Код аптеки");
                MessageBox.Show("Заявки загружены");
            }
            else MessageBox.Show("Не выбраны аптеки");
        }

        private void DownloadPrice()
        {
            int i = 0;
            string[] sup = new string[3] { "", "", "" };

            DataView view = new DataView(dt_sup);
            DataTable distinctValues = view.ToTable(true, namechcol, "ИД базы", "Код поставщика");
            for (int ii = 0; ii < distinctValues.Rows.Count; ii++)
                if (!Convert.ToBoolean(distinctValues.Rows[ii][namechcol])) distinctValues.Rows[ii].Delete();


            foreach (DataRow dr in distinctValues.Rows)
                if (Convert.ToBoolean(dr[namechcol]))
                {
                    switch (Convert.ToInt32(dr["ИД базы"]))
                    {
                        case 1:
                            sup[0] += dr["Код поставщика"].ToString() + ",";
                            break;
                        case 2:
                            sup[1] += dr["Код поставщика"].ToString() + ",";
                            break;
                        case 3:
                            sup[2] += dr["Код поставщика"].ToString() + ",";
                            break;
                    }
                    i++;
                }

            if (i > 0)
            {
                SqlCommand price = conn.CreateCommand();
                price.CommandText = "pas_FillPrice";
                price.CommandType = CommandType.StoredProcedure;
                price.CommandTimeout = 600;
                price.Parameters.Add("@idbase", SqlDbType.Int);
                price.Parameters.Add("@sup", SqlDbType.VarChar, 1000);

                for (int j = 0; j < sup.Length; j++)
                {
                    if (sup[j] != "")
                    {
                        price.Parameters["@idbase"].Value = j + 1;
                        price.Parameters["@sup"].Value = sup[j].Substring(0, sup[j].Length - 1);
                        if (conn.State == ConnectionState.Closed) conn.Open();
                        price.ExecuteNonQuery();
                    }
                }

                dt_sup = DownloadData(conn, dt_sup, "pas_SupplSel_DAC");
                dt_sup = DataTableUpd(dt_sup, supfl, "Код поставщика");

    /* Загрузка прайсов с нулевыми кодами Маптеки*/
                foreach (DataRow dr in dt_sup.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]))
                    {
                        dt_sup_code.Rows.Add(dr[1], dr[5], dr[6]);
                    }

                }

                //DataView v_sup = new DataView(dt_sup_code);
                //DataTable distVal = v_sup.ToTable(true, "Код поставщика", "ИД базы");

                //foreach (DataRow row_ph in distVal.Rows)
                //{
                //    SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[2];
                //    par[0].name = "@idbase";
                //    par[0].value = row_ph["ИД базы"];
                //    par[1].name = "@sup";
                //    par[1].value = row_ph["Код поставщика"].ToString();
                //    int rezult;                                 
                //    rezult = (new SqlStorProc()).SqlProcExecNonQuery(conn, "pas_FillPriceNULL", 300, par);
                //    conn.Close();
                //}
               MessageBox.Show("Прайсы загружены");
            }
            else MessageBox.Show("Не выбраны поставщики");
        }

        private DataTable DataTableUpd(DataTable dt, Pharmflag[] pfl,string codename)
        {
            foreach (DataRow dr in dt.Rows)
                foreach(Pharmflag pf in pfl)
                    if (pf.code == Convert.ToInt32(dr[codename]) && pf.baseid == Convert.ToInt32(dr["ИД базы"]) && pf.cityid == Convert.ToInt32(dr["ИД города"]))
                        dr[namechcol] = pf.fl;
            return dt;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            ShowHideRows(checkBox1.Checked,checkBox4.Checked);
        }

        private void ShowHideRows(bool hide,bool price)
        {
            if (dt_price != null)
            {
                return;
            }
            if (dataGridView1.DataSource != null)
            {
                cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
                cm.SuspendBinding();
                if (dataGridView1.RowCount > 0)
                {
                    foreach (DataGridViewRow dr in dataGridView1.Rows)
                    {
                        if (price)
                        {
                            if (dr.Cells["Цена сегодня"].Value == DBNull.Value) dr.Cells[0].Value = false;
                            else dr.Cells[0].Value = true;
                        }
                        else dr.Cells[0].Value = true;
                        if (hide)
                        {
                            if (Convert.ToBoolean(dr.Cells[0].Value)) dr.Visible = true;
                            else dr.Visible = false;
                        }
                        else dr.Visible = true;   
                    }
                }
                cm.ResumeBinding();
            }
        }

        private void dataGridView1_DataSourceChanged(object sender, EventArgs e)
        {
            ShowHideRows(checkBox1.Checked, checkBox4.Checked);
        }

        /// Отправка письма на почтовый ящик C# mail send
        /// <param name="smtpServer">Имя SMTP-сервера</param>
        /// <param name="from">Адрес отправителя</param>
        /// <param name="password">пароль к почтовому ящику отправителя</param>
        /// <param name="mailto">Адрес получателя</param>
        /// <param name="caption">Тема письма</param>
        /// <param name="message">Сообщение</param>
        /// <param name="attachFile">Присоединенный файл</param>
        private static void SendMail(string smtpServer, string from , string password,string mailto, string caption, string message, string attachFile, bool del)
        {
            try
            {
                MailMessage mail = new MailMessage();
                mail.From = new MailAddress(from);
                mail.To.Add(new MailAddress(mailto));
                mail.CC.Add(new MailAddress(from));
                mail.Subject = caption;
                mail.Body = message;
                if (!string.IsNullOrEmpty(attachFile))
                mail.Attachments.Add(new Attachment(attachFile));
                SmtpClient client = new SmtpClient();
                client.Host = smtpServer;
                client.Port = 587;
                client.UseDefaultCredentials = true;
                client.DeliveryMethod = SmtpDeliveryMethod.Network;
                client.Send(mail);
                mail.Dispose();
                if (!string.IsNullOrEmpty(attachFile) && del) File.Delete(attachFile);
            }
            catch (Exception e)
            {
                throw new Exception("Mail.Send: " + e.Message);
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            if (textBox1.Text != "")
            {
                try
                {
                    if (dt_price != null)
                    {
                        //- отправка писем с прайсами на крайнюю дату
                        int send1 = 0;
                        DownloadPharmemail();
                        DataView view = new DataView(dt_ph_name);
                        DataTable distinctValues = view.ToTable(true, "Город", "Код аптеки", "Аптека");
                        DataRow r_dV = distinctValues.Rows[0];
                        string file;
                        if (flag == true)
                        {
                            if (r_dV["Город"].ToString() == "Гордеевка.Нижний новгород")
                            {
                                r_dV["Город"] = "Нижний новгород";
                            }
                            file = CreateExcelPrice("Прайс привязанных позиций город " + r_dV["Город"] + " " + DateTime.Now.Date.ToShortDateString(), dataGridView1);
                        }
                        else
                        {
                            file = CreateExcelPrice("Прайс непривязанных позиций город " + r_dV["Город"] + " " + DateTime.Now.Date.ToShortDateString(), dataGridView1);
                        }

                        foreach (DataRow row_ph in distinctValues.Rows)
                        {
                            bool fl = false;
                            foreach (DataRow email in dt_email.Rows)
                            {
                                if (Convert.ToInt32(email["Код аптеки"]) == Convert.ToInt32(row_ph["Код аптеки"]))
                                {
                                    SendMail("192.168.0.9", textBox1.Text, "", email[2].ToString(), "Прайс для аптеки " + row_ph["Аптека"].ToString() + " " + DateTime.Now.Date.ToShortDateString(), "Добрый день", file, false);
                                    //SendMail("192.168.0.9", textBox1.Text, "", "mgushina@apteka-rayona.ru", "Прайс для аптеки " + row_ph["Аптека"].ToString() + " " + DateTime.Now.Date.ToShortDateString(), "Добрый день", file, false);
                                    send1++;
                                    fl = true;
                                }
                            }
                            if (!fl)
                            {
                                Form4 f4 = new Form4();
                                f4.caption = row_ph["Аптека"].ToString();
                                f4.email = "";
                                f4.ShowDialog();
                                string m = f4.email;
                                f4.Close();
                                f4 = null;
                                if (m != "")
                                {
                                    SendMail("192.168.0.9", textBox1.Text, "", m, "Прайс для аптеки " + row_ph["Аптека"].ToString() + " " + DateTime.Now.Date.ToShortDateString(), "Добрый день", file, false);
                                    //SendMail("192.168.0.9", textBox1.Text, "", "mgushina@apteka-rayona.ru", "Прайс для аптеки " + row_ph["Аптека"].ToString() + " " + DateTime.Now.Date.ToShortDateString(), "Добрый день", file, false);
                                    send1++;
                                }
                            }
                            Thread.Sleep(2000);
                        }
                        MessageBox.Show("Всего отправлено писем: " + send1.ToString());
                        if (checkBox2.Checked) File.Delete(file);
                        dt_price = null;
                        return;
                    }

                    checkBox4.Checked = true;
                    int send = 0;
                    DownloadPharmemail();
                    DataTable dt_tmp = new DataTable();
                    dt_tmp.Columns.Add("Код аптеки");
                    dt_tmp.Columns.Add("Аптека");
                    DataRow[] foundRows = dt_rep.Select("Отказ = 1");

                    foreach (DataRow dr in foundRows)
                    {
                        object[] row = new object[2] { dr[1], dr[2] };
                        dt_tmp.Rows.Add(row);
                    }
                    DataView viewPrice = new DataView(dt_tmp);
                    DataTable distinctValuesPr = viewPrice.ToTable(true, "Код аптеки", "Аптека");

                    foreach (DataRow row_ph in distinctValuesPr.Rows)
                    {
                        bool fl = false;
                        string file = CreateExcel(row_ph["Аптека"].ToString().Replace(@"/", "др.") + " " + DateTime.Now.Date.ToShortDateString(), dataGridView1, Convert.ToInt32(row_ph["Код аптеки"]));
                        foreach (DataRow email in dt_email.Rows)
                        {
                            if (Convert.ToInt32(email["Код аптеки"]) == Convert.ToInt32(row_ph["Код аптеки"]))
                            {
                                SendMail("192.168.0.9", textBox1.Text, "", email[2].ToString(), "Отказ по цене " + row_ph["Аптека"].ToString() + " " + DateTime.Now.Date.ToShortDateString(), "Добрый день", file, false);
                                //SendMail("192.168.0.9", textBox1.Text, "", "ddynaev@apteka-rayona.ru", "Отказ по цене " + row_ph["Аптека"].ToString() + " " + DateTime.Now.Date.ToShortDateString(), "Добрый день", file, false);
                                send++;
                                fl = true;
                            }
                        }
                        if (!fl)
                        {
                            Form4 f4 = new Form4();
                            f4.caption = row_ph["Аптека"].ToString();
                            f4.email = "";
                            f4.ShowDialog();
                            string m = f4.email;
                            f4.Close();
                            f4 = null;
                            if (m != "")
                            {
                                SendMail("192.168.0.9", textBox1.Text, "", m, "Отказ по цене " + row_ph["Аптека"].ToString() + " " + DateTime.Now.Date.ToShortDateString(), "Добрый день", file, false);                     
                                //SendMail("192.168.0.9", textBox1.Text, "", "ddynaev@apteka-rayona.ru", "Отказ по цене " + row_ph["Аптека"].ToString() + " " + DateTime.Now.Date.ToShortDateString(), "Добрый день", file, false);
                                send++;
                            }
                        }
                        if (checkBox2.Checked) File.Delete(file);
                        Thread.Sleep(2000);
                    }
                    MessageBox.Show("Всего отправлено писем: " + send.ToString());
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else MessageBox.Show("Проверьте электронные адреса");
        }

        private string CreateExcel(string filename, DataGridView dgv, int pharmcode)
        {

            if (dgv.DataSource != null && filename != "")
            {
                try
                {
                    if (!checkBox3.Checked)
                    {
                        FolderBrowserDialog folder_d = new FolderBrowserDialog();
                        folder_d.SelectedPath = folder;
                        folder_d.ShowNewFolderButton = true;
                        if (folder_d.ShowDialog() == DialogResult.OK && folder_d.SelectedPath != folder)
                            folder = folder_d.SelectedPath;
                    }
                    if (folder == "")
                        filename = System.Windows.Forms.Application.StartupPath + "\\" + filename + ".xls"; //xlsx
                    else filename = folder + "\\" + filename + ".xls"; //".xlsx"

                    Hashtable myHashtable = new Hashtable();
                    myHashtable = CheckExcellProcesses(myHashtable);

                    Excel.Application exapp = new Excel.ApplicationClass();
                    Excel.Workbook exwb = exapp.Workbooks.Add(System.Reflection.Missing.Value);
                    Excel.Worksheet exws = (Excel.Worksheet)exwb.Sheets[1];

                    foreach (DataGridViewColumn col in dgv.Columns)
                    {
                        if (col.Index > 3 && col.Index < 7)
                        {
                            exapp.Cells[1, col.Index - 3] = col.Name;
                            exapp.get_Range(exapp.Cells[1, col.Index - 3], exapp.Cells[1, col.Index - 3]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                            exapp.get_Range(exapp.Cells[1, col.Index - 3], exapp.Cells[1, col.Index - 3]).Cells.Interior.Color = Excel.XlRgbColor.rgbGrey;
                        }
                        if (col.Index == 9)
                        {
                            exapp.Cells[1, 4] = col.Name;
                            exapp.get_Range(exapp.Cells[1, 4], exapp.Cells[1, 4]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                            exapp.get_Range(exapp.Cells[1, 4], exapp.Cells[1, 4]).Cells.Interior.Color = Excel.XlRgbColor.rgbGrey;
                        }
                    }
                    int r_ind = 2;
                    bool fl = false;
                    foreach (DataGridViewRow row in dgv.Rows)
                    {
                        fl = false;
                        foreach (DataGridViewCell cell in row.Cells)
                        {
                            if (Convert.ToBoolean(row.Cells[0].Value) && Convert.ToInt32(row.Cells["Код аптеки"].Value) == pharmcode)
                            {
                                if (cell.ColumnIndex > 3 && cell.ColumnIndex < 7)
                                {
                                    exapp.Cells[r_ind, cell.ColumnIndex - 3] = cell.Value;
                                    exapp.get_Range(exapp.Cells[r_ind, cell.ColumnIndex - 3], exapp.Cells[r_ind, cell.ColumnIndex - 3]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                }
                                if (cell.ColumnIndex == 9)
                                {
                                    exapp.Cells[r_ind, 4] = cell.Value;
                                    exapp.get_Range(exapp.Cells[r_ind, 4], exapp.Cells[r_ind, 4]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                }
                                fl = true;
                            }
                        }
                        if (fl) r_ind++;
                    }
                    exwb.SaveAs(filename,
                        Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null,
                        Excel.XlSaveAsAccessMode.xlExclusive, null, null, null, null, null);
                    exwb.Close(Type.Missing, Type.Missing, Type.Missing);
                    exapp.Quit();
                    KillExcel(myHashtable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else return "";
            return filename;
        }

//      создание файла для прайса 
        private string CreateExcelPrice(string filename, DataGridView dgv)
        {
            if (dgv.DataSource != null && filename != "")
            {
            try
                {
                    string filezip;
                    string file = DateTime.Now.Date.ToShortDateString() + ".xls";
                    if (!checkBox3.Checked)
                    {
                        FolderBrowserDialog folder_d = new FolderBrowserDialog();
                        folder_d.SelectedPath = folder;
                        folder_d.ShowNewFolderButton = true;
                        if (folder_d.ShowDialog() == DialogResult.OK && folder_d.SelectedPath != folder)
                            folder = folder_d.SelectedPath;
                    }
                    if (folder == "")
                    {
                        filezip = System.Windows.Forms.Application.StartupPath + "\\" + filename + ".zip";
                        filename = System.Windows.Forms.Application.StartupPath + "\\" + filename + ".xls"; //xlsx
                    }
                    else
                    {
                        filezip = folder + "\\" + filename + ".zip";
                        filename = folder + "\\" + filename + ".xls"; //".xlsx"
                    }

                    

                    Hashtable myHashtable = new Hashtable();
                    myHashtable = CheckExcellProcesses(myHashtable);

                    Excel.Application exapp = new Excel.ApplicationClass();
                    Excel.Workbook exwb = exapp.Workbooks.Add(System.Reflection.Missing.Value);
                    Excel.Worksheet exws = (Excel.Worksheet)exwb.Sheets[1];
                    
                    foreach (DataGridViewColumn col in dgv.Columns)
                        {
                            exapp.Cells[1, col.Index+1] = col.Name;
                        }

                    int countcol = dt_price.Columns.Count;
                    int countrow = dt_price.Rows.Count;
                    object[,] dt_buf = ToArray(dt_price);

                    exws.get_Range(exws.Cells[2, 1], exws.Cells[countrow + 1, countcol]).Value2 = dt_buf;
                    exwb.SaveAs(filename,
                            Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null,
                            Excel.XlSaveAsAccessMode.xlExclusive, null, null, null, null, null);

                    exwb.Close(Type.Missing, Type.Missing, Type.Missing);
                    exapp.Quit();
                    KillExcel(myHashtable);
                    using (ZipFile zip = new ZipFile()) // Создаем объект для работы с архивом
                    {
                       string newName = file;
                       zip.CompressionLevel = Ionic.Zlib.CompressionLevel.Level9; // Задаем максимальную степень сжатия 
                       zip.AddFile(filename , "\\").FileName = newName; // Кладем в архив одиночный файл
                       zip.Save(filezip); // Создаем архив     
                    }
                    
                 File.Delete(filename);
                 filename = filezip;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

           
            }
            else return "";
            return filename;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                FolderBrowserDialog folder_d = new FolderBrowserDialog();
                folder_d.SelectedPath = folder;
                folder_d.ShowNewFolderButton = true;
                if (folder_d.ShowDialog() == DialogResult.OK && folder_d.SelectedPath != folder)
                {
                    if(conf.State == ConnectionState.Closed) conf.Open();
                    SqlCommand comf = conf.CreateCommand();
                    comf.CommandType = CommandType.StoredProcedure;
                    comf.CommandText = "FolderDefaulInsUpd";
                    comf.Parameters.AddWithValue("@tool", ToolName);
                    comf.Parameters.AddWithValue("folder", folder_d.SelectedPath);
                    comf.ExecuteNonQuery();
                    folder = folder_d.SelectedPath;
                    MessageBox.Show("Путь по умолчанию изменен на\n" + folder);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            Form2 f2 = new Form2();
            f2.conn = pharmolapdw;
            f2.ShowDialog();
            f2.conn.Close();
            f2.Close();
            f2 = null;
        }

        private void button7_Click(object sender, EventArgs e)
        {
            Form3 f3 = new Form3();
            f3.conn = pharmolapdw;
            f3.ShowDialog();
            f3.conn.Close();
            f3.Close();
            f3 = null;
        }

        private void DownloadPharmemail()
        {
            dt_email = null;
            dt_email = new DataTable();
            SqlCommand comm = pharmolapdw.CreateCommand();
            comm.CommandText = "pas_PharmEmailSel";
            comm.CommandType = CommandType.StoredProcedure;
            if (pharmolapdw.State == ConnectionState.Closed) pharmolapdw.Open();
            dt_email.Load(comm.ExecuteReader());
            pharmolapdw.Close();
        }

        private void checkBox4_CheckedChanged(object sender, EventArgs e)
        {
            ShowHideRows(checkBox1.Checked, checkBox4.Checked);
        }

        private void button8_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand emins = conf.CreateCommand();
                emins.CommandType = CommandType.StoredProcedure;
                emins.CommandText = "pas_EmailUpd";
                emins.Parameters.AddWithValue("@email", textBox1.Text);
                if (conf.State == ConnectionState.Closed) conf.Open();
                emins.ExecuteNonQuery();
                MessageBox.Show("Адрес электронной почты обновлен");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private Hashtable CheckExcellProcesses(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            int iCount = 0;
            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            return myHashtable;
        }

        private void KillExcel(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }
            AllProcesses = null;
        }
        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            // Прайс по привязанным позициям
            flag = true;
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            // Прайс по непривязанным позициям
            flag = false;
        }
        // Перевод в двумерный массив DataTable
        public static object[,] ToArray(DataTable data)
        {
            var ret = Array.CreateInstance(typeof(object), data.Rows.Count, data.Columns.Count) as object[,];
            for (var i = 0; i < data.Rows.Count; i++)
                for (var j = 0; j < data.Columns.Count; j++)
                    ret[i, j] = data.Rows[i][j];
            return ret;
        }

        private void button10_Click(object sender, EventArgs e)
        {
            try
            {
                if (dt_price != null)
                {
                    //- отправка писем с прайсами на крайнюю дату
                    DownloadPharmemail();
                    DataView view = new DataView(dt_ph_name);
                    DataTable distinctValues = view.ToTable(true, "Город", "Код аптеки", "Аптека");
                    DataRow r_dV = distinctValues.Rows[0];
                    if (flag == true)
                    {
                        if (r_dV["Город"].ToString() == "Гордеевка.Нижний новгород")
                            r_dV["Город"] = "Нижний новгород";
                        CreateExcelPrice("Прайс привязанных позиций город " + r_dV["Город"] + " " + DateTime.Now.Date.ToShortDateString(), dataGridView1);
                    }
                    else
                        CreateExcelPrice("Прайс непривязанных позиций город " + r_dV["Город"] + " " + DateTime.Now.Date.ToShortDateString(), dataGridView1);

                    //dt_price = null;
                    MessageBox.Show("Прайсы сохранены\nПапка:\n" + folder);
                    return;
                }

                DataTable dt_tmp = new DataTable();
                dt_tmp.Columns.Add("Код аптеки");
                dt_tmp.Columns.Add("Аптека");
                DataRow[] foundRows = dt_rep.Select("Отказ = 1");

                foreach (DataRow dr in foundRows)
                {
                    object[] row = new object[2] { dr[1], dr[2] };
                    dt_tmp.Rows.Add(row);
                }
                DataView viewPrice = new DataView(dt_tmp);
                DataTable distinctValuesPr = viewPrice.ToTable(true, "Код аптеки", "Аптека");

                foreach (DataRow row_ph in distinctValuesPr.Rows)
                    CreateExcel(row_ph["Аптека"].ToString().Replace(@"/", "др.") + " " + DateTime.Now.Date.ToShortDateString(), dataGridView1, Convert.ToInt32(row_ph["Код аптеки"]));

                MessageBox.Show("Отказы по цене сохранены\nПапка:\n" + folder);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

    }
}
